package com.gome.ocean.controller.datasource;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.net.URLEncoder;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.http.client.methods.HttpGet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.Base64Utils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLDeleteStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.ast.statement.SQLUpdateStatement;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.github.pagehelper.Page;
import com.gome.framework.util.DateUtil;
import com.gome.framework.util.JsonUtil;
import com.gome.framework.util.SystemInfoUtil;
import com.gome.ocean.common.constants.VelocityContants;
import com.gome.ocean.common.constants.WebSqlContants;
import com.gome.ocean.common.enums.DataBaseType;
import com.gome.ocean.common.utils.CompressFileUtils;
import com.gome.ocean.common.utils.GddlParseHelper;
import com.gome.ocean.common.utils.HttpClientUtil;
import com.gome.ocean.common.utils.ResponsePage;
import com.gome.ocean.dao.model.datasource.DataSourceBean;
import com.gome.ocean.dao.model.datasource.SQLUploadBean;
import com.gome.ocean.dao.model.datasource.SQLUploadRecordBean;
import com.gome.ocean.dao.model.webservice.Model_Permissions;
import com.gome.ocean.service.base.AuthorityBaseController;
import com.gome.ocean.service.datax.ISQLUploadRecordService;
import com.gome.ocean.service.datax.ISQLUploadService;
import com.gome.ocean.service.mail.EmailSubject;
import com.gome.ocean.service.mail.SimpleMailSender;
import com.gome.ocean.service.velocity.VelocityBean;
import com.gome.ocean.service.webservice.permission.InterfaceResult;
import com.gome.ocean.service.webservice.permission.PermissionsServiceSoap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

/**
 * @author zhangguoqing 2016年10月26日 下午5:10:45
 * @since 5.0.0
 */
@Controller
@RequestMapping("/sqlupload")
public class SQLUploadController extends AuthorityBaseController {
 
    private static Logger           LOG = LoggerFactory.getLogger(SQLUploadController.class);

    @Autowired
    private VelocityBean            velocityBean;
    @Autowired
    private SimpleMailSender        simpleMailSender;
    @Autowired
    private ISQLUploadService       uploadService;
    @Autowired
    private ISQLUploadRecordService isqlUploadRecordService;
    @Autowired
    private PermissionsServiceSoap  permissionsServiceSoap;

    private Map<String, Integer> getNOContainsWhereSQLNum(String sqlStr, String dbType) throws RuntimeException {
        Map<String, Integer> map = Maps.newHashMap();
        Integer countNum = 0;
        Integer grantCountNum = 0;
        DataBaseType base;
        if (DataBaseType.Oracle.getTypeFullName().toLowerCase().equals(dbType.toLowerCase())) {
            base = DataBaseType.Oracle;
        } else {
            base = DataBaseType.MySql;
        }
        String[] sqlTextArray = sqlStr.split(";");
        for (String sql : sqlTextArray) {
            if (StringUtils.isBlank(sql)) continue;
            try {
                if (sql.toLowerCase().contains("grant")) {
                    grantCountNum++;
                } else {
                    SQLStatement sqlStatement = GddlParseHelper.parseStatement(sql, base);
                    if (sqlStatement instanceof SQLSelectStatement) {
                        if (!sql.toLowerCase().contains("where")) {
                            countNum++;
                        }
                    } else if (sqlStatement instanceof SQLUpdateStatement) {
                        if (!sql.toLowerCase().contains("where")) {
                            countNum++;
                        }
                    } else if (sqlStatement instanceof SQLDeleteStatement) {
                        if (!sql.toLowerCase().contains("where")) {
                            countNum++;
                        }
                    }
                }
            } catch (Exception e) {// 暂时不做处理了
                map.put("grantKey", grantCountNum);
                map.put("whereKey", 0);
                return map;
            }
        }
        map.put("grantKey", grantCountNum);
        map.put("whereKey", countNum);
        return map;
    }

    @RequestMapping(value = "/getSqlUploadGet", method = RequestMethod.GET)
    public ModelAndView getSqlUploadGet(HttpServletRequest request, ModelAndView mav) {
        Map<String, Object> map = Maps.newHashMap();
        map.put("ptype", WebSqlContants.GROUP_DB);
        map.put("email", this.getCurrentUserObj().getEmail());
        map.put("env_type", this.getDiffEnvironmentValue());
        map.put("gtype", WebSqlContants.GROUP_TYPE_APP);
        HttpClientUtil clientUtil = new HttpClientUtil();
        HttpGet httpGet = HttpClientUtil.getGetRequest();
        String path;
        try {
            path = WebSqlContants.getFormatAuthorityGroupPathStr(this.getDBAInterfaceIp(),
                URLEncoder.encode(JsonUtil.toJson(map), "UTF-8"));
            URL url = new URL(path);
            httpGet.setURI(url.toURI());
            String json = clientUtil.executeAndGetWithFailedRetry(httpGet, 6, 10001);
            List<DataSourceBean> datasouceLists = JSONArray.parseArray(json, DataSourceBean.class);
            mav.addObject("datasouceLists", datasouceLists);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String jsonParam = JSON.toJSONString(map);
        mav.addObject("jsonParam", jsonParam);
        mav.addObject("ipVal", this.getDBAInterfaceIp());
        List<String> projectLists = this.getCurrentUserProjects();
        mav.addObject("projectLists", projectLists);

        mav.setViewName("datasource/sql-upload-insert");
        return mav;
    }

    @RequestMapping(value = "/insertSqlUploadPost", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> insertSqlUploadPost(HttpServletRequest request,
                                                   @RequestParam(value = "files", required = false) MultipartFile files) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            SQLUploadBean bean = new SQLUploadBean();
            Long sqlId = new Date().getTime();
            bean.setSqlId(sqlId);
            String commitEmail = this.getCurrentUserObj().getEmail();
            bean.setEmail(commitEmail);
            String auserEmail = request.getParameter("email");
            bean.setAuser(auserEmail);
            bean.setAuserEmail(auserEmail);
            if (StringUtils.isEmpty(this.getDepartMentByAccount(auserEmail.split("@")[0]))) {
                map.put("root",
                    "提交数据失败，您当前输入的部门负责人账户(" + auserEmail + ")在OA系统中不存在,请确定部门负责人的账户地址为正确邮件地址! \n如不理解,请联系管理员");
                map.put("failure", false);
                return map;
            }
            bean.setEdept(this.getDepartMentByAccount(auserEmail.split("@")[0]));
            String sqlType = request.getParameter("sqlType");
            bean.setSqlType(sqlType);
            if (sqlType.equals("e sql")) {
                bean.setUserName(request.getParameter("userName"));
            }
            String dbName = request.getParameter("dbName");
            if (StringUtils.isNotEmpty(dbName)) {
                bean.setDbName(dbName);
            }
            String uploadType = request.getParameter("uploadType");
            bean.setUploadType(uploadType);
            bean.setAppVersion("V1.0.0");
            String sqlText = request.getParameter("sqlTextEditor");
            if (StringUtils.isNotBlank(sqlText)) {
                String dbType = request.getParameter("dbType");
                Map<String, Integer> countNumMap = this.getNOContainsWhereSQLNum(sqlText, dbType);
                if (countNumMap.get("whereKey") >= 1) {
                    map.put("root", "提交失败,您有[" + countNumMap.get("whereKey") + "]条SQL内容不包含where规范,请检查并修改后重新提交,谢谢!");
                    map.put("failure", false);
                    return map;
                }
                if (countNumMap.get("grantKey") >= 1) {
                    map.put("root",
                        "提交失败,不能提交授权语句,请联系DBA处理授权grant语句！ \r\n 您有[" + countNumMap.get("grantKey")
                                    + "]条SQL内容包含权限处理grant关键字,请检查并修改后重新提交,谢谢!");
                    map.put("failure", false);
                    return map;
                }
            }
            bean.setSqlText(sqlText);
            String sqlBackup = request.getParameter("sqlBackupEditor");
            bean.setSqlBackup(sqlBackup);
            Date date = new Date();
            bean.setCreateTime(date);
            bean.setAstatus("0");
            String usages = request.getParameter("sqlUsage");
            bean.setUsages(usages);
            String remark = request.getParameter("remark");
            bean.setRemark(remark);
            String project = request.getParameter("project");
            bean.setProject(project);
            if (files != null && files.getSize() > 0) {
                if (files.getSize() > 1024000) {
                    map.put("root", "抱歉,您所提交的文件已超过1M最大限制,请重新上传规格内文件!");
                    map.put("failure", false);
                    return map;
                }
                try {
                    byte[] fileByte = files.getBytes();
                    String filesByteStr = Base64Utils.encodeToString(fileByte);
                    bean.setSqlFile(filesByteStr);
                    bean.setFiles(files.getOriginalFilename());
                } catch (IOException e) {
                    LOG.error(e.getMessage(), e);
                }
            }
            String[] adIds = StringUtils.split(request.getParameter("adId").toString(),",");
            boolean flag = false;
            for(String adId : adIds){
            	if(StringUtils.isNotEmpty(adId)){
            		SQLUploadBean newBean = new SQLUploadBean();
                	BeanUtils.copyProperties(bean, newBean);
                	newBean.setAdId(Long.parseLong(adId));
                	flag = uploadService.insert(newBean);
            	}
            }
        	if (flag) {
                map.put("root", "提交成功");
                map.put("success", true);
                return map;
            } else {
                map.put("root", "提交失败");
                map.put("failure", false);
                return map;
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            map.put("root", "提交失败\r\n" + ExceptionUtils.getMessage(e));
            map.put("failure", false);
            return map;
        }

    }

    @RequestMapping(value = "/sqlUploadFindGet", method = RequestMethod.GET)
    public ModelAndView sqlUploadFindGet(HttpServletRequest request, ModelAndView mav) {
        Map<String, Object> map = Maps.newHashMap();
        map.put("ptype", WebSqlContants.GROUP_DB);
        map.put("email", this.getCurrentUserObj().getEmail());
        map.put("env_type", this.getDiffEnvironmentValue());
        map.put("gtype", WebSqlContants.GROUP_TYPE_APP);
        HttpClientUtil clientUtil = new HttpClientUtil();
        HttpGet httpGet = HttpClientUtil.getGetRequest();
        String path;
        try {
            path = WebSqlContants.getFormatAuthorityGroupPathStr(this.getDBAInterfaceIp(),
                URLEncoder.encode(JsonUtil.toJson(map), "UTF-8"));
            URL url = new URL(path);
            httpGet.setURI(url.toURI());
            String json = clientUtil.executeAndGetWithFailedRetry(httpGet, 6, 10001);
            List<DataSourceBean> datasouceLists = JSONArray.parseArray(json, DataSourceBean.class);
            mav.addObject("datasouceLists", datasouceLists);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String jsonParam = JSON.toJSONString(map);
        mav.addObject("jsonParam", jsonParam);
        mav.addObject("ipVal", this.getDBAInterfaceIp());
        List<String> projectLists = this.getCurrentUserProjects();
        mav.addObject("projectLists", projectLists);

        mav.setViewName("datasource/sql-upload-find");
        return mav;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/findSqlUploadDataPost", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage findSqlUploadDataPost(HttpServletRequest request) {
        ResponsePage page = new ResponsePage();
        Integer pageNum = this.getPageNum();
        Integer pageSize = this.getPageSize();
        String commitUserEmail = request.getParameter("commitUserEmail");
        String startTime = request.getParameter("startTime");
        String endTime = request.getParameter("endTime");
        String usages = request.getParameter("usages");
        String astatus = request.getParameter("astatus");
        Map<String, Object> resultMap = Maps.newHashMap();
        resultMap.put("commitUserEmail", commitUserEmail);
        resultMap.put("startTime", startTime);
        resultMap.put("endTime", endTime);
        resultMap.put("usages", usages);
        resultMap.put("astatus", astatus);
        if (isAdmin() || isDBA()) {
            Page<SQLUploadBean> sqlPage = uploadService.getListsBeanByMap(resultMap, pageNum, pageSize);
            return page.setPage(sqlPage, "查询成功");
        } else {
            List<String> projects = this.getCurrentUserProjects();
            if (projects != null && projects.size() > 0) {
                Page<SQLUploadBean> pagePro = uploadService.getListsBeanByProjects(projects,
                    commitUserEmail,
                    startTime,
                    endTime,
                    pageNum,
                    pageSize);
                return page.setPage(pagePro, "查询成功");
            } else {
                return page.setErrorMsg("请选择加入某一个项目组");
            }
        }
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/removeSqlUploadData", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage removeSqlUploadData(HttpServletRequest request) {
        ResponsePage page = new ResponsePage();
        Long id = this.getLongParamValue("id");
        try {
            boolean flag = uploadService.delete(id);
            if (flag) {
                return page.setSuccessMsg("删除成功");
            }
            return page.setErrorMsg("删除失败");
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            return page.setErrorMsg("删除失败");
        }

    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/findSingleBeanSqlUpload", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage findSingleBeanSqlUpload(HttpServletRequest request) {
        ResponsePage page = new ResponsePage();
        Long id = Long.parseLong(request.getParameter("id"));
        try {
            Map<String, Object> map = Maps.newHashMap();
            map.put("id", id);
            SQLUploadBean bean = uploadService.getBeanByMap(map);
            return page.setSingleObject(bean, "查询成功");
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            return page.setErrorMsg("查询失败");
        }
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/updateSqlUploadById", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage updateSqlUploadById(HttpServletRequest request,
                                            @RequestParam(value = "files", required = false) MultipartFile files) {
        ResponsePage page = new ResponsePage();
        try {
            Long id = Long.parseLong(request.getParameter("id"));
            Map<String, Object> map = Maps.newHashMap();
            map.put("id", id);
            SQLUploadBean bean = uploadService.getBeanByMap(map);
            Long adId = Long.parseLong(request.getParameter("adId"));
            bean.setAdId(adId);
            Long sqlId = new Date().getTime();
            bean.setSqlId(sqlId);
            String auserEmail = request.getParameter("email");
            bean.setAuser(auserEmail);
            bean.setAuserEmail(auserEmail);
            if (StringUtils.isEmpty(this.getDepartMentByAccount(auserEmail.split("@")[0]))) {
                return page.setErrorMsg(
                    "修改数据失败，您当前输入的部门负责人账户(" + auserEmail + ")在OA系统中不存在,请确定部门负责人的账户地址为正确邮件地址! \n如不理解,请联系管理员");
            }
            bean.setEdept(this.getDepartMentByAccount(auserEmail.split("@")[0]));
            String commitEmail = this.getCurrentUserObj().getEmail();
            bean.setEmail(commitEmail);
            String dbName = request.getParameter("dbName");
            if (StringUtils.isNotEmpty(dbName)) {
                bean.setDbName(dbName);
            }
            String sqlType = request.getParameter("sqlType");
            bean.setSqlType(sqlType);
            if (sqlType.equals("e sql")) {
                bean.setUserName(request.getParameter("userName"));
            }
            String uploadType = request.getParameter("uploadType");
            bean.setUploadType(uploadType);
            bean.setAppVersion("V1.0.0");
            String sqlText = request.getParameter("sqlText");
            if (StringUtils.isNotBlank(sqlText)) {
                String dbType = request.getParameter("dbType");
                Map<String, Integer> countNumMap = this.getNOContainsWhereSQLNum(sqlText, dbType);
                if (countNumMap.get("whereKey") >= 1) {
                    return page
                        .setErrorMsg("提交失败,您有[" + countNumMap.get("whereKey") + "]条SQL内容不包含where规范,请检查并修改后重新提交,谢谢!");
                }
                if (countNumMap.get("grantKey") >= 1) {
                    return page.setErrorMsg("提交失败,不能提交授权语句,请联系DBA处理授权grant语句！ \r\n 您有[" + countNumMap.get("grantKey")
                                            + "]条SQL内容包含权限处理grant关键字,请检查并修改后重新提交,谢谢!");
                }
            }
            bean.setSqlText(sqlText);
            String sqlBackup = request.getParameter("sqlBackup");
            bean.setSqlBackup(sqlBackup);
            Date date = new Date();
            bean.setCreateTime(date);
            bean.setAstatus("0");
            String usages = request.getParameter("usages");
            bean.setUsages(usages);
            String remark = request.getParameter("remark");
            bean.setRemark(remark);
            String project = request.getParameter("project");
            bean.setProject(project);
            if (files != null && files.getSize() > 0) {
                if (files.getSize() > 204800) {
                    return page.setErrorMsg("修改失败,抱歉,您所提交的文件已超过200KB最大限制,请重新上传规格内文件!");
                }
                try {
                    byte[] fileByte = files.getBytes();
                    String filesByteStr = Base64Utils.encodeToString(fileByte);
                    bean.setSqlFile(filesByteStr);
                    bean.setFiles(files.getOriginalFilename());
                } catch (IOException e) {
                    LOG.error(e.getMessage(), e);
                }
            }
            boolean flag = uploadService.update(bean);
            if (flag) {
                return page.setSuccessMsg("修改成功");
            } else {
                return page.setErrorMsg("修改失败");
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            return page.setSuccessMsg("修改成功");
        }

    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/cancelSqlUploadById", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage cancelSqlUploadById(HttpServletRequest request, String id) {
        ResponsePage page = new ResponsePage();
        Long uId = Long.parseLong(id);
        // 判断是否重复作废
        List<SQLUploadRecordBean> sqlRecLists = isqlUploadRecordService.getBeanListBySqlId(uId);
        for (SQLUploadRecordBean rec : sqlRecLists) {
            if (rec.getCountNum() != null) {
                if (StringUtils.isNotBlank(rec.getCheckStatus())) {
                    if (rec.getCheckStatus().equals(WebSqlContants.STATUS_128))
                        return page.setErrorMsg("抱歉,该数据已经作废,请不要重复点击作废！谢谢!");
                }
            }
        }
        Map<String, Object> map = Maps.newHashMap();
        map.put("ptype", WebSqlContants.SQL_STATUS);
        map.put("astatus", WebSqlContants.STATUS_128);
        map.put("sn", this.getDiffEnvironmentValue().concat("-") + uId);
        String jsonParam = JSON.toJSONString(map);
        String ip = this.getDBAInterfaceIp();
        String path = WebSqlContants.getFormatSQLUploadPathStr(ip, jsonParam);
        Map<String, Object> callMap = this.executeHttpClientPostUrl(path);
        if (callMap != null && callMap.size() > 0) {
            if (callMap.get("state").equals("success")) {
                SQLUploadBean sqlUpload = new SQLUploadBean();
                SQLUploadRecordBean sqlRecBean = new SQLUploadRecordBean();
                sqlRecBean.setCheckStatus(WebSqlContants.STATUS_128);
                sqlRecBean.setCheckMsg("发起人已作废");
                sqlRecBean.setCountNum(1L);
                sqlRecBean.setSqlUploadId(uId);
                sqlUpload.setId(uId);
                sqlUpload.setAstatus(WebSqlContants.STATUS_128);
                sqlUpload.setUpdateTime(new Date());
                sqlUpload.setAtime(new Date());
                uploadService.update(sqlUpload);
                isqlUploadRecordService.insertBean(sqlRecBean);
                return page.setSuccessMsg("已处理成功，作废已提交DBA");
            } else if (callMap.get("state").equals("error")) {
                return page.setErrorMsg("处理失败,DBA接口异常信息为：\n" + callMap.get("info"));
            }
        }
        return page.setErrorMsg("出现异常,DBA接口返回为空");
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping("/ajaxCommitEmailInterface")
    @ResponseBody
    public ResponsePage ajaxCommitEmailInterface(HttpServletRequest request) {
        ResponsePage page = new ResponsePage();
        try {
            Map<String, Object> map = Maps.newHashMap();
            Long id = Long.parseLong(request.getParameter("SQLUPMId"));
            map.put("id", id);
            SQLUploadBean sqlBean = uploadService.getBeanByMap(map);
            sqlBean.setAstatus(WebSqlContants.STATUS_2);
            String text = "";
            if (StringUtils.isEmpty(sqlBean.getSqlText())) {
                text = "无";
            } else {
                text = sqlBean.getSqlText();
            }
            String fileContent = "";
            File file = null;
            if (StringUtils.isEmpty(sqlBean.getSqlFile())) {
                fileContent = "无";
            } else {
                fileContent = sqlBean.getFiles();
                // 暂时注释,待扩容后，取消注释
                byte[] fileDetailBt = Base64Utils.decodeFromString(sqlBean.getSqlFile());
                String outFileName = String.valueOf(new Date().getTime()).concat("_") + fileContent;
                file = CompressFileUtils.byteToFile("/app/download_file", outFileName, fileDetailBt);
            }
            Map<String, Object> paramMaps = new HashMap<String, Object>();
            paramMaps.put("submitUser", getCurrentUser());
            if (text.length() > 3000) {
                text = text.substring(0, 3000) + "...";
            }
            paramMaps.put("executeDB", StringUtils.isEmpty(sqlBean.getDbName()) ? "" : sqlBean.getDbName());
            paramMaps.put("executeSQL", "文本内容:" + text + "<br/> 附件内容:" + fileContent);
            paramMaps.put("submitTime", DateUtil.format(new Date(), "yyyy-MM-dd HH:mm:ss"));

            String port = SystemInfoUtil.getServerPort();
            String ip = SystemInfoUtil.getLocalAddress();
            StringBuffer httpadr = new StringBuffer();
            httpadr.append("http://").append(ip).append(":").append(port).append(request.getContextPath());
            String yes = httpadr.toString() + "/permissionMag/checkDataUpdateStatusInterface?mainId=" + sqlBean.getId()
                         + "&status=1";
            String no = httpadr.toString() + "/permissionMag/checkDataUpdateStatusInterface?mainId=" + sqlBean.getId()
                        + "&status=0";
            paramMaps.put("yes", yes);
            paramMaps.put("no", no);
            String authorityContent = velocityBean.assemblePlugins(VelocityContants.AUTHORITY_MAIL, paramMaps);
            String[] auserArray = sqlBean.getAuserEmail().split(",");
            List<String> repients = Lists.newArrayList();
            for (String auserEmail : auserArray) {
                repients.add(auserEmail);
            }
            simpleMailSender.sendContentAndFileEmail(repients, EmailSubject.SQLAUTHORITY, authorityContent, file);

            SQLUploadRecordBean sqlRecBean = new SQLUploadRecordBean();
            sqlRecBean.setCreateTime(new Date());
            sqlRecBean.setDealTime(new Date());
            sqlRecBean.setSqlUploadId(id);
            sqlRecBean.setDealStatus(WebSqlContants.STATUS_16);
            sqlRecBean.setCheckStatus(WebSqlContants.STATUS_2);
            sqlRecBean.setDbaMsg("已提交给DBA,如有特殊情况,请联系DBA");
            isqlUploadRecordService.insertBean(sqlRecBean);
            boolean flag = uploadService.update(sqlBean);
            if (flag) {
                return page.setSuccessMsg("邮件发送成功");
            } else {
                return page.setErrorMsg("状态更新失败");
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            return page.setErrorMsg("邮件发送失败！" + ExceptionUtils.getMessage(e));
        }
    }

    // 发送邮件之前 先发送DBA
    @RequestMapping("/findStatusCheckProgress")
    @ResponseBody
    public Map<String, Object> findStatusCheckProgress(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        Long sqlId = Long.parseLong(request.getParameter("sqlId"));
        List<SQLUploadRecordBean> sqlRecordLists = isqlUploadRecordService.getBeanListBySqlId(sqlId);
        if (sqlRecordLists != null && sqlRecordLists.size() > 0) {
            map.put("sqlRecordLists", sqlRecordLists);
            map.put("success", true);
            map.put("msg", "查询成功");
            return map;
        }
        map.put("failure", true);
        map.put("msg", "查询失败,暂时没有进度");
        return map;
    }

    @RequestMapping("/commitDBAInterfaceData")
    @ResponseBody
    public Map<String, Object> commitDBAInterfaceData(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            Long id = Long.parseLong(request.getParameter("SQLUPMId"));
            map.put("id", id);
            SQLUploadBean sqlBean = uploadService.getBeanByMap(map);
            sqlBean.setAstatus(WebSqlContants.STATUS_2);
            String jsonParams = this.parseBeanJSONStrSendDBAInterface(sqlBean);
            map.put("params", jsonParams);
            map.put("ip", this.getDBAInterfaceIp());
            map.put("sqlType", sqlBean.getSqlType());
            map.put("adid", sqlBean.getAdId());
            map.put("success", true);
            map.put("info", "参数组合成功");
            return map;
        } catch (Exception e) {
            map.put("success", false);
            map.put("info", "组合参数出现异常" + ExceptionUtils.getMessage(e));
        }
        return map;
    }

    private String parseBeanJSONStrSendDBAInterface(SQLUploadBean sqlBean) {
        Map<String, Object> dbaExecuParams = Maps.newHashMap();
        Map<String, Object> sqlFileMap = Maps.newHashMap();
        if (StringUtils.isNotEmpty(sqlBean.getSqlFile())) {
            sqlFileMap.put("file_name", sqlBean.getFiles());
            sqlFileMap.put("file_content", sqlBean.getSqlFile());
        }
        dbaExecuParams.put("ptype", "upload");
        dbaExecuParams.put("sn", this.getDiffEnvironmentValue().concat("-") + sqlBean.getId());
        dbaExecuParams.put("adid", sqlBean.getAdId());
        dbaExecuParams.put("sql_id", sqlBean.getSqlId());
        dbaExecuParams.put("sql_type", StringUtils.isEmpty(sqlBean.getSqlType()) ? "" : sqlBean.getSqlType());
        dbaExecuParams.put("upload_type", StringUtils.isEmpty(sqlBean.getUploadType()) ? "" : sqlBean.getUploadType());
        dbaExecuParams.put("app_version", StringUtils.isEmpty(sqlBean.getAppVersion()) ? "" : sqlBean.getAppVersion());
        dbaExecuParams.put("sql_text", StringUtils.isEmpty(sqlBean.getSqlText()) ? "" : sqlBean.getSqlText());
        dbaExecuParams.put("sql_file", StringUtils.isEmpty(sqlBean.getSqlFile()) ? "" : sqlFileMap);
        dbaExecuParams.put("sql_backup", StringUtils.isEmpty(sqlBean.getSqlBackup()) ? "" : sqlBean.getSqlBackup());
        dbaExecuParams.put("email", StringUtils.isEmpty(sqlBean.getEmail()) ? "" : sqlBean.getEmail());
        dbaExecuParams.put("edept", StringUtils.isEmpty(sqlBean.getEdept()) ? "" : sqlBean.getEdept());
        dbaExecuParams.put("auser", StringUtils.isEmpty(sqlBean.getAuser()) ? "" : sqlBean.getAuser());
        dbaExecuParams.put("atime", sqlBean.getAtime() == null ? new Date() : sqlBean.getAtime());
        dbaExecuParams.put("astatus", sqlBean.getAstatus());
        dbaExecuParams.put("usage", StringUtils.isEmpty(sqlBean.getUsages()) ? "" : sqlBean.getUsages());
        return JSON.toJSONString(dbaExecuParams);
    }

    @RequestMapping("/lookLinkSqlUpRecord")
    @ResponseBody
    public Map<String, Object> lookLinkSqlUpRecord(HttpServletRequest request, String id, String status) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            Long uId = Long.parseLong(id);
            SQLUploadRecordBean sqlRecBean = isqlUploadRecordService.getDealSuccessBeanByMap(uId, status);
            if (sqlRecBean != null) {
                if (sqlRecBean.getDbaMsg().contains("http")) {
                    String httpLink = sqlRecBean.getDbaMsg().substring(sqlRecBean.getDbaMsg().indexOf("http"),
                        sqlRecBean.getDbaMsg().lastIndexOf("html") + 4);
                    map.put("success", true);
                    map.put("result", httpLink);
                    return map;
                }
            }
            map.put("success", false);
            map.put("result", "无结果可视化页面,请点击【查看进度】按钮!");
        } catch (Exception e) {
            map.put("success", false);
            map.put("result", "异常信息:\r\n" + ExceptionUtils.getMessage(e));
        }
        return map;
    }

    @RequestMapping("/commitOAInterfaceData")
    @ResponseBody
    public Map<String, Object> commitOAInterfaceData(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            Long oaCmId = Long.parseLong(request.getParameter("sqlOaCmId"));
            map.put("id", oaCmId);
            SQLUploadBean sqlBean = uploadService.getBeanByMap(map);
            if (sqlBean != null) {
                InterfaceResult result = new InterfaceResult();
                Model_Permissions sqlExportPerm = new Model_Permissions();
                sqlExportPerm.setId(oaCmId.toString());
                sqlExportPerm.setFormType("13");
                sqlExportPerm.setAccountNum(sqlBean.getEmail().split("@")[0]);
                sqlExportPerm.setApplyUser(sqlBean.getUserName());
                sqlExportPerm.setEmail(sqlBean.getEmail());
                sqlExportPerm.setDepartment(sqlBean.getEdept());
                sqlExportPerm.setDbName(sqlBean.getDbName());
                sqlExportPerm.setExportSql(sqlBean.getSqlText());
                sqlExportPerm.setAuthPurpose(sqlBean.getUsages());
                sqlExportPerm.setRemark(sqlBean.getRemark());
                sqlExportPerm.setDepartMag(sqlBean.getAuserEmail());
                sqlExportPerm.setAppName("");
                sqlExportPerm.setServiceIpAddr("");
                sqlExportPerm.setAuthGroup("");
                sqlExportPerm.setDeadline("");
                sqlExportPerm.setMember("");
                sqlExportPerm.setTableStructJson("");

                result = permissionsServiceSoap.tranDataToOA(sqlExportPerm);
                if (result.isIsSuccess()) {
                    sqlBean.setAstatus("2");
                    sqlBean.setUpdateTime(new Date());
                    uploadService.update(sqlBean);
                    SQLUploadRecordBean sqlRecBean = new SQLUploadRecordBean();
                    sqlRecBean.setCreateTime(new Date());
                    sqlRecBean.setDealTime(new Date());
                    sqlRecBean.setCheckTime(new Date());
                    sqlRecBean.setDealStatus(WebSqlContants.STATUS_16);
                    sqlRecBean.setDbaMsg("已提交DBA人员执行,请等待！");
                    sqlRecBean.setSqlUploadId(oaCmId);
                    sqlRecBean.setCheckStatus(WebSqlContants.STATUS_2);
                    sqlRecBean.setCheckMsg("已提交申请人OA系统,请到OA中填写审批流程负责人！");
                    isqlUploadRecordService.insertBean(sqlRecBean);
                }
                map.put("success", result.isIsSuccess());
                map.put("msg", result.getFaultMsg());
                return map;
            }
            map.put("success", false);
            map.put("msg", "表单提交失败,请联系管理员");
            return map;
        } catch (Exception e) {
            map.put("success", false);
            map.put("failure", true);
            map.put("msg", "异常信息:\r\n" + ExceptionUtils.getMessage(e));
            return map;
        }
    }

    @RequestMapping("/updateSQLUploadStatus")
    @ResponseBody
    public Map<String, Object> updateSQLUploadStatus(HttpServletRequest request, Long sqlUpId) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            map.put("id", sqlUpId);
            SQLUploadBean sqlBean = uploadService.getBeanByMap(map);
            sqlBean.setAstatus("8");
            sqlBean.setUpdateTime(new Date());
            uploadService.update(sqlBean);
            SQLUploadRecordBean sqlRecBean = new SQLUploadRecordBean();
            sqlRecBean.setCreateTime(new Date());
            sqlRecBean.setDealTime(new Date());
            sqlRecBean.setCheckTime(new Date());
            sqlRecBean.setDealStatus(WebSqlContants.STATUS_16);
            sqlRecBean.setDbaMsg("已提交DBA人员执行,请等待！");
            sqlRecBean.setSqlUploadId(sqlUpId);
            sqlRecBean.setCheckStatus(WebSqlContants.STATUS_8);
            sqlRecBean.setCheckMsg("已提交系统,请等待返回结果！");
            isqlUploadRecordService.insertBean(sqlRecBean);
            map.put("success", true);
            map.put("msg", "成功");
            return map;
        } catch (Exception e) {
            map.put("success", false);
            map.put("failure", true);
            map.put("msg", "异常信息:\r\n" + ExceptionUtils.getMessage(e));
            return map;
        }
    }
}
